大家好，我是一位程式菜鳥，這邊將會分享我刷題的過程與遇到的問題，
第一站是SQL!!會先附上題目、遇到的問題，最後是該題的程式碼。

595. Big Countries

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| name        | varchar |
| continent   | varchar |
| area        | int     |
| population  | int     |
| gdp         | int     |
+-------------+---------+
name is the primary key column for this table.
Each row of this table gives information about the name of a country, the continent to which it belongs, its area, the population, and its GDP value.

A country is big if:

it has an area of at least three million (i.e., 3000000 km2), or
it has a population of at least twenty-five million (i.e., 25000000).
Write an SQL query to report the name, population, and area of the big countries.

Return the result table in any order.

The query result format is in the following example. 

Example 1:

Input: 
World table:
+-------------+-----------+---------+------------+--------------+
| name        | continent | area    | population | gdp          |
+-------------+-----------+---------+------------+--------------+
| Afghanistan | Asia      | 652230  | 25500100   | 20343000000  |
| Albania     | Europe    | 28748   | 2831741    | 12960000000  |
| Algeria     | Africa    | 2381741 | 37100000   | 188681000000 |
| Andorra     | Europe    | 468     | 78115      | 3712000000   |
| Angola      | Africa    | 1246700 | 20609294   | 100990000000 |
+-------------+-----------+---------+------------+--------------+
Output: 
+-------------+------------+---------+
| name        | population | area    |
+-------------+------------+---------+
| Afghanistan | 25500100   | 652230  |
| Algeria     | 37100000   | 2381741 |
+-------------+------------+---------+

如果像我一樣英文不好就直接丟Google翻譯，題意是要我們篩選出
**面積至少為三百萬（即 3000000 平方公里），或
至少有 2500 萬人口（即 2500 萬）。

SELECT Name, Population, Area  /*選需要的欄位*/
FROM World
WHERE population >= 25000000 or
area >= 3000000;   /*列出篩選條件*/

P.S.一開始遇到的問題是leetcode Run時會通過，提交時會出錯，
花了些時間爬文發現篩選條件 ">=" 這邊不能只寫 ">" ，一定要 ">=" 才會通過。
下面是別的大神分享用"UNION"能查更快，
原理是將這兩個結果合併，省去重新程式查詢的時間。

SELECT name, population, area
FROM world
WHERE area >= 3000000
UNION
SELECT name, population, area
FROM world
WHERE population >= 25000000;

1757. Recyclable and Low Fat Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| low_fats    | enum    |
| recyclable  | enum    |
+-------------+---------+
product_id is the primary key for this table.
low_fats is an ENUM of type ('Y', 'N') where 'Y' means this product is low fat and 'N' means it is not.
recyclable is an ENUM of types ('Y', 'N') where 'Y' means this product is recyclable and 'N' means it is not.
 
Write an SQL query to find the ids of products that are both low fat and recyclable.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
Products table:
+-------------+----------+------------+
| product_id  | low_fats | recyclable |
+-------------+----------+------------+
| 0           | Y        | N          |
| 1           | Y        | Y          |
| 2           | N        | Y          |
| 3           | Y        | Y          |
| 4           | N        | N          |
+-------------+----------+------------+
Output: 
+-------------+
| product_id  |
+-------------+
| 1           |
| 3           |
+-------------+
Explanation: Only products 1 and 3 are both low fat and recyclable.

**題目希望我們列出同時'低脂'與'可回收'的產品ID
從表格輸出能看到共同點，欄位"low_fats"跟"recyclable"都是"Y"的情況

SELECT product_id FROM Products
WHERE low_fats='Y' AND recyclable='Y'; /*用AND表示要同時符合這兩個條件*/

584. Find Customer Referee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| referee_id  | int     |
+-------------+---------+
id is the primary key column for this table.
Each row of this table indicates the id of a customer, their name, and the id of the customer who referred them.

Write an SQL query to report the names of the customer that are not referred by the customer with id = 2.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
Customer table:
+----+------+------------+
| id | name | referee_id |
+----+------+------------+
| 1  | Will | null       |
| 2  | Jane | null       |
| 3  | Alex | 2          |
| 4  | Bill | null       |
| 5  | Zack | 1          |
| 6  | Mark | 2          |
+----+------+------------+
Output: 
+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+

**這題要求找出"referee_id"不是2的名字，如果直接寫WHERE referee_id !=2;會被判定不通過，
要讓referee_id的值不為NULL 提交才會判定通過，解法是用ifnull<<讓裡面的null=1才能順利提交。
P.S. ifnull這個函式是在問Google為什麼Run ok， Submit卻失敗時知道的解法。

SELECT name FROM Customer 
WHERE ifnull(referee_id,1) !=2; /*用ifnull使null值為1*/

183. Customers Who Never Order

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table indicates the ID and name of a customer.
 

Table: Orders

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| customerId  | int  |
+-------------+------+
id is the primary key column for this table.
customerId is a foreign key of the ID from the Customers table.
Each row of this table indicates the ID of an order and the ID of the customer who ordered it.

Write an SQL query to report all customers who never order anything.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
Customers table:
+----+-------+
| id | name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Orders table:
+----+------------+
| id | customerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
Output: 
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

**這題是要列出沒有購買的客戶名字，由於有Customers、Orders兩個表，
需要先關聯表單後再做篩選，看到大神有用AS來做命名，就學著做了。
底下會註解大神的思路。

SELECT TB1.NAME AS Customers /*5.最後輸出TB1.NAME欄位改稱Customers*/
FROM Customers AS TB1        /*1.讓Customers改稱TB1*/
LEFT JOIN Orders AS TB2      /*2.關聯Orders並讓Orders改稱TB2*/
ON TB1.Id = TB2.customerId   /*3.用ON表示TB2的customerId要加入TB1的Id欄*/
WHERE TB2.Id IS NULL;        /*4.條件為TB2的Id欄是NULL，表示沒訂購過*/

P.S.我自己最後寫成WHERE TB2.customerId IS NULL <<用customerId的空值也行，
可以想像TB2是連在TB1右側，用戶Joe、Sam他們有訂購紀錄的值，找NULL自然就能篩出Henry、Max。

最後謝謝各位看到這邊，希望新手在LeetCode 584、595這兩題遇到Run ok卻Submit錯誤時也能避開這些時間成本。